﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using OfficeOpenXml;
using Telerik.Web.UI;
using System.IO;
using System.Data.OleDb;
using System.Text;
using System.Web.UI;
using System.Windows.Forms;

/// <summary>
/// Summary description for ExcelUtil
/// </summary>
public class ExcelHeader
{
    public string DataField { get; set; }
    public string HeaderText { get; set; }
    public int MergerY { get; set; }
    public int MergerX { get; set; }
}
public class ExcelUtil
{

    public static List<ExcelHeader> CustomHeaders = new List<ExcelHeader>();
    public static List<string> CustomFooters = new List<string>();
    public static List<string> AllowDataField = new List<string>();
    const int rowLimit = 65000;

    public ExcelUtil()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    private static string getCustomHeader()
    {
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        //Create header with merger
        sb.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\"> ");
        sb2.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\" >");
        int i = 1;  //Index of cell: Index is position of no merger cell . i is number of column
        bool addIndex = true;
        foreach (var item in CustomHeaders)
        {
            if (item.MergerX + item.MergerY > 0)
            {
                addIndex = true;        //Allow add index of no merger cell
                if (item.MergerX > 0)
                {
                    sb.Append("<Cell ss:MergeAcross=\"" + item.MergerX + "\" ss:StyleID=\"myHeader\"><Data ss:Type=\"String\">" + item.HeaderText + "</Data></Cell>");
                }
                else
                {
                    sb.Append("<Cell ss:MergeDown=\"" + item.MergerY + "\" ss:StyleID=\"myHeader\"><Data ss:Type=\"String\">" + item.HeaderText + "</Data></Cell>");
                    i++;    //Index of cell
                }
            }
            else
            {

                if (addIndex)
                {
                    sb2.Append("<Cell  ss:StyleID=\"myHeader\" ss:Index=\"" + i + "\"><Data ss:Type=\"String\">" + item.HeaderText + "</Data></Cell>");
                    addIndex = false;
                    i++;    //Index of cell
                    continue;
                }
                else
                {
                    i++;    //Index of cell
                    sb2.Append("<Cell ss:StyleID=\"myHeader\"><Data ss:Type=\"String\">" + item.HeaderText + "</Data></Cell>");
                }

            }

        }
        sb.Append("</Row>");
        sb2.Append("</Row>");



        return sb.ToString() + sb2.ToString();
    }
    private static string getCustomFooter()
    {
        StringBuilder sb2 = new StringBuilder();
        if (CustomFooters != null)
        {
            sb2.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\" >");
            foreach (var item in CustomFooters)
            {
                string value = item;
                if (Utils.IsNumeric(item.Replace(",", "")))
                {
                    sb2.Append("<Cell ss:StyleID=\"myHeaderNumber\" ><Data ss:Type=\"Number\">" + value + "</Data></Cell>");
                }
                else
                {
                    sb2.Append("<Cell ss:StyleID=\"myHeader\" ><Data ss:Type=\"String\">" + value + "</Data></Cell>");
                }
            }
            sb2.Append("</Row>");
        }
        return sb2.ToString();
    }
    private static string replaceXmlChar(string input)
    {
        input = input.Replace("&", "&");
        input = input.Replace("<", "<");
        input = input.Replace(">", ">");
        input = input.Replace("'", "&apos;");
        return input;
    }

    private static void DataTableToExcel(
        DataTable dt, String filename,
        List<ExcelCell> headers, List<ExcelCell> footers, String sheetname = "report")
    {
        using (ExcelPackage xlPackage = new ExcelPackage())
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(sheetname);

            //Write Header

            int colHead = 1;
            foreach (ExcelCell item in headers)
            {
                worksheet.Cells[1, colHead].Value = item.Value;
                worksheet.Cells[1, colHead].Style.Font.Bold = true;
                colHead++;
            }
            worksheet.Cells["A2"].LoadFromDataTable(dt, false);
            //Write Row
            //const int startRow = 2;
            //int row = startRow;
            //int col = 1;
            //foreach (DataRow dr in dt.Rows)
            //{
            //    foreach (DataColumn dc in dt.Columns)
            //    {
            //        object value = dr[dc];
            //        if ((dc.DataType).FullName == "System.DateTime" && (dc.DataType).Name == "DateTime")
            //        {
            //            worksheet.Cells[row, col].Style.Numberformat.Format = "dd/MM/yyyy hh:mm:ss"; //OR "yyyy-mm-dd h:mm" if you want to include the time!
            //            worksheet.Column(col).Width = 25;
            //        }
            //        worksheet.Cells[row, col].Value = value;
            //        col++;
            //    }
            //    col = 1;
            //    row++;
            //}

            //Write Row 2
            const int startRow = 2;
            int row = startRow;
            int col = 1;
            foreach (DataColumn dc in dt.Columns)
            {
                if ((dc.DataType).FullName == "System.DateTime" && (dc.DataType).Name == "DateTime")
                {
                    worksheet.Column(col).Width = 25;
                    worksheet.Column(col).Style.Numberformat.Format = "dd/MM/yyyy hh:mm:ss";
                }
                col++;
            }

            //Write Footer
            int colFooter = 1;
            if (footers != null)
            {
                foreach (ExcelCell item in footers)
                {
                    worksheet.Cells[row + dt.Rows.Count, colFooter].Value = item.Value;
                    colFooter++;
                }
            }


            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.Clear();
            xlPackage.SaveAs(Response.OutputStream);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename + ".xlsx");
            Response.End();
        }

    }

    private static string DataTableToCSV(DataTable dt)
    {
        StringWriter sb = new StringWriter();
        //Write Row
        foreach (DataRow row in dt.Rows)
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Write(row[i].ToString() + "\t");
            }
            sb.Write(Environment.NewLine);
        }


        return sb.ToString();
    }


    private static DataTable WorksheetToDataTable(ExcelWorksheet worksheet)
    {
        /*
        // Vars
        var dt = new DataTable();
        var rowCnt = worksheet.Dimension.End.Row;
        var colCnt = worksheet.Dimension.End.Column + 1;

        bool hasData = false;
        int rowNull = -1;
        // Loop through Columns
        for (var c = 1; c < colCnt; c++)
        {
            //if (hasData == false)
            //{
            //    break;
            //}

            // Add Column
            string columnName = worksheet.Cells[1, c].Text;
            dt.Columns.Add(new DataColumn(columnName));

            // Loop through Rows
            for (var r = 1; r < rowCnt; r++)
            {

                if (worksheet.Cells[r + 1, 1].Value == null )
                {
                    hasData = true;
                    break;
                }
                // Add Row
                if (dt.Rows.Count <= (rowCnt - 1))
                {
                    if (!hasData)
                        dt.Rows.Add(dt.NewRow());
                    // Populate Row
                    dt.Rows[r - 1][c - 1] = worksheet.Cells[r + 1, c].Value;
                }

            }
        }

        // Return
        return dt;
         */
        DataTable tbl = new DataTable();
        using (var pck = new OfficeOpenXml.ExcelPackage())
        {

            var ws = worksheet;           
            bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            var startRow = hasHeader ? 2 : 1;
           
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                 int col = 0;
                try
                {
                    var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                    var row = tbl.NewRow();                   
                    bool flag = false;
                    foreach (var cell in wsRow)
                    {
                        if (col == 0 && cell.Text.Count() == 0)
                        {
                            flag = true;
                            break;
                        }
                        row[cell.Start.Column - 1] = cell.Text;
                        col++;                        
                    }
                    if (!flag && (row[0].ToString().Count() > 0 || row[1].ToString().Count() > 0 || row[2].ToString().Count() > 0))
                    {
                        tbl.Rows.Add(row);
                    }
                    else
                    {
                        break;
                    }
                }
                catch (Exception ex)
                {
                   // MessageBox.Show("Loi du lieu dong: " + rowNum.ToString() + " - Cot: " + col.ToString());
                    ControlUtil.Log("-------------------------------");
                    ControlUtil.Log("Import ton kho cham luan chuyen");
                    ControlUtil.Log("Loi du lieu dong: " + rowNum.ToString() + " - Cot: " + col.ToString());
                    ControlUtil.Log(ex.ToString());
                    ControlUtil.Log("----------------------------");
                    DataTable dtError = new DataTable();
                    dtError.Columns.Add("Error");
                    DataRow dr = dtError.NewRow();
                    dr["Error"]="Loi du lieu dong: " + rowNum.ToString() + " - Cot: " + col.ToString();
                    dtError.Rows.Add(dr);
                    return dtError;
                }
            }
            
            return tbl;
        }
    }

    public static void ExportDataTableToExcel2007FromRadGrid(DataTable dt, RadGrid tempGrid,
        string filename = "result", List<ExcelCell> headers = null,
        List<ExcelCell> footers = null)
    {
        int rgColumCount = tempGrid.Columns.Count;
        int dtColumnCount = dt.Columns.Count;


        List<string> AllowDataField = new List<string>();

        if (headers == null)
        {
            headers = new List<ExcelCell>();
            for (int i = 0; i < rgColumCount; i++)
            {
                string datafield = string.Empty;
                string x = tempGrid.Columns[i].ColumnType;
                if (tempGrid.Columns[i].ColumnType == "GridBoundColumn")
                {
                    GridBoundColumn myColumn = (GridBoundColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                else if (tempGrid.Columns[i].ColumnType == "GridTemplateColumn")
                {
                    GridTemplateColumn myColumn = (GridTemplateColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                if (dt.Columns[datafield] != null && tempGrid.Columns[i].HeaderText.Length != 0 && tempGrid.Columns[i].Visible)
                {
                    int ordinal = i;
                    if (ordinal >= dt.Columns.Count)
                    {
                        ordinal = dt.Columns.Count - 1;
                    }
                    dt.Columns[datafield].SetOrdinal(ordinal);
                    dt.Columns[datafield].ColumnName = tempGrid.Columns[i].HeaderText;
                    ExcelCell header = new ExcelCell();
                    header.Value = tempGrid.Columns[i].HeaderText;
                    headers.Add(header);
                    AllowDataField.Add(header.Value);
                }
            }
        }
        else
        {
            AllowDataField = headers.Select(x => x.DataField).ToList();
        }
        if (footers == null)
        {
            footers = new List<ExcelCell>();
        }
        //Remove DataField
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!AllowDataField.Contains(dt.Columns[i].ColumnName) || dt.Columns[i].ColumnName == "")
            {
                dt.Columns.Remove(dt.Columns[i].ColumnName);
                i--;
            }
        }
        //dt.Columns[0].DataType = typeof( System.Int64);
        DataTableToExcel(dt, filename, headers, footers);

    }

    public static DataTable Import(string filePath)
    {
        if (Path.GetExtension(filePath) == ".xlsx")
        {
            DataTable dt = new DataTable();
            // Get the file we are going to process
            FileInfo existingFile = new FileInfo(filePath);
            // Open and read the XlSX file.
            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                // Get the work book in the file
                ExcelWorkbook workBook = package.Workbook;
                if (workBook != null)
                {
                    if (workBook.Worksheets.Count > 0)
                    {
                        // Get the first worksheet
                        ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                        dt = WorksheetToDataTable(currentWorksheet);
                    }
                }
            }
            return dt;
        }
        else
        {
            return ImportExcelXLS(filePath, true);
        }


    }
    public static DataTable ImportExcelXLS(string FileName, bool hasHeaders)
    {
        string HDR = hasHeaders ? "Yes" : "No";
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

        DataSet output = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(strConn))
        {
            conn.Open();

            DataTable schemaTable = conn.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                string sheet = schemaRow["TABLE_NAME"].ToString();

                if (!sheet.EndsWith("_"))
                {
                    try
                    {
                        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                        cmd.CommandType = CommandType.Text;

                        DataTable outputTable = new DataTable(sheet);
                        output.Tables.Add(outputTable);
                        new OleDbDataAdapter(cmd).Fill(outputTable);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
                    }
                }
            }
        }
        return output.Tables[0];
    }
    private static DataTable ImportXLS(string filePath)
    {
        string FileName = HttpContext.Current.Server.MapPath(filePath);
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;";

        OleDbConnection oledbConn = new OleDbConnection(connString);
        oledbConn.Open();
        OleDbCommand cmd = new OleDbCommand("SELECT * FROM [cdhh1$] ", oledbConn);
        OleDbDataAdapter oleda = new OleDbDataAdapter();
        oleda.SelectCommand = cmd;
        DataSet ds = new DataSet();
        oleda.Fill(ds, "Allocations");
        oledbConn.Close();
        oledbConn.Dispose();
        return ds.Tables[0];

    }



    public static void ExportToExcel(DataTable dt, List<ExcelCell> headers, List<ExcelCell> footers = null, string filename = "report")
    {
        DataTableToExcel(dt, filename, headers, footers);
    }
    public static void ExportToCSV(DataTable dt, List<ExcelCell> headers, List<ExcelCell> footers = null, string filename = "report")
    {
        HttpResponse resp = HttpContext.Current.Response;
        HttpContext context = HttpContext.Current;
        string content = content = String.Join("\t", headers.Select(x => x.Value));
        content += Environment.NewLine;
        content += DataTableToCSV(dt);

        context.Response.Clear();
        context.Response.AddHeader("content-disposition", "attachment; filename=" + filename + ".csv");
        context.Response.ContentType = "text/csv";

        using (StreamWriter writer = new StreamWriter(context.Response.OutputStream, Encoding.UTF32))
        {
            writer.Write(content);
        }
        context.Response.End();
    }
    public static void ExportToCSV(DataTable dt, RadGrid tempGrid,
        string filename = "result", List<ExcelCell> headers = null,
        List<ExcelCell> footers = null)
    {
        int rgColumCount = tempGrid.Columns.Count;
        int dtColumnCount = dt.Columns.Count;


        List<string> AllowDataField = new List<string>();

        if (headers == null)
        {
            headers = new List<ExcelCell>();
            for (int i = 0; i < rgColumCount; i++)
            {
                string datafield = string.Empty;
                string x = tempGrid.Columns[i].ColumnType;
                if (tempGrid.Columns[i].ColumnType == "GridBoundColumn")
                {
                    GridBoundColumn myColumn = (GridBoundColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                else if (tempGrid.Columns[i].ColumnType == "GridTemplateColumn")
                {
                    GridTemplateColumn myColumn = (GridTemplateColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                if (dt.Columns[datafield] != null && tempGrid.Columns[i].HeaderText.Length != 0 && tempGrid.Columns[i].Visible)
                {
                    int ordinal = i;
                    if (ordinal >= dt.Columns.Count)
                    {
                        ordinal = dt.Columns.Count - 1;
                    }
                    dt.Columns[datafield].SetOrdinal(ordinal);
                    dt.Columns[datafield].ColumnName = tempGrid.Columns[i].HeaderText;
                    ExcelCell header = new ExcelCell();
                    header.Value = tempGrid.Columns[i].HeaderText;
                    headers.Add(header);
                    AllowDataField.Add(header.Value);
                }
            }
        }
        else
        {
            AllowDataField = headers.Select(x => x.DataField).ToList();
        }
        if (footers == null)
        {
            footers = new List<ExcelCell>();
        }
        //Remove DataField
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!AllowDataField.Contains(dt.Columns[i].ColumnName) || dt.Columns[i].ColumnName == "")
            {
                dt.Columns.Remove(dt.Columns[i].ColumnName);
                i--;
            }
        }
        //dt.Columns[0].DataType = typeof( System.Int64);
        ExportToCSV(dt, headers, footers, filename);

    }


    //New Export Excel many footer
    public static void ExportDataTableToExcel2007FromRadGridFooters(DataTable dt, RadGrid tempGrid,
        string filename = "result", List<ExcelCell> headers = null,
        List<List<ExcelCell>> footers = null)
    {
        int rgColumCount = tempGrid.Columns.Count;
        int dtColumnCount = dt.Columns.Count;


        List<string> AllowDataField = new List<string>();

        if (headers == null)
        {
            headers = new List<ExcelCell>();
            for (int i = 0; i < rgColumCount; i++)
            {
                string datafield = string.Empty;
                string x = tempGrid.Columns[i].ColumnType;
                if (tempGrid.Columns[i].ColumnType == "GridBoundColumn")
                {
                    GridBoundColumn myColumn = (GridBoundColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                else if (tempGrid.Columns[i].ColumnType == "GridTemplateColumn")
                {
                    GridTemplateColumn myColumn = (GridTemplateColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                if (dt.Columns[datafield] != null && tempGrid.Columns[i].HeaderText.Length != 0 && tempGrid.Columns[i].Visible)
                {
                    int ordinal = i;
                    if (ordinal >= dt.Columns.Count)
                    {
                        ordinal = dt.Columns.Count - 1;
                    }
                    dt.Columns[datafield].SetOrdinal(ordinal);
                    dt.Columns[datafield].ColumnName = tempGrid.Columns[i].HeaderText;
                    ExcelCell header = new ExcelCell();
                    header.Value = tempGrid.Columns[i].HeaderText;
                    headers.Add(header);
                    AllowDataField.Add(header.Value);
                }
            }
        }
        else
        {
            AllowDataField = headers.Select(x => x.DataField).ToList();
        }
        if (footers == null)
        {
            List<ExcelCell> footer = new List<ExcelCell>();
            footers.Add(footer);
        }
        //Remove DataField
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (!AllowDataField.Contains(dt.Columns[i].ColumnName) || dt.Columns[i].ColumnName == "")
            {
                dt.Columns.Remove(dt.Columns[i].ColumnName);
                i--;
            }
        }
        DataTableToExcelFooters(dt, filename, headers, footers);
    }

    private static void DataTableToExcelFooters(
       DataTable dt, String filename,
       List<ExcelCell> headers, List<List<ExcelCell>> footers, String sheetname = "report")
    {
        using (ExcelPackage xlPackage = new ExcelPackage())
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(sheetname);

            //Write Header

            int colHead = 1;
            foreach (ExcelCell item in headers)
            {
                worksheet.Cells[1, colHead].Value = item.Value;
                worksheet.Cells[1, colHead].Style.Font.Bold = true;
                colHead++;
            }
            worksheet.Cells["A2"].LoadFromDataTable(dt, false);

            //Write Row 2
            const int startRow = 2;
            int row = startRow;
            int col = 1;
            foreach (DataColumn dc in dt.Columns)
            {
                if ((dc.DataType).FullName == "System.DateTime" && (dc.DataType).Name == "DateTime")
                {
                    worksheet.Column(col).Width = 25;
                    worksheet.Column(col).Style.Numberformat.Format = "dd/MM/yyyy hh:mm:ss";
                }
                col++;
            }

            //Write Footer
            int colFooter = 1;
            if (footers != null)
            {
                int rowfooter = row + dt.Rows.Count;
                foreach (List<ExcelCell> footer in footers)
                {

                    foreach (ExcelCell item in footer)
                    {
                        worksheet.Cells[rowfooter, colFooter].Value = item.Value;
                        colFooter++;
                    }
                    rowfooter++;
                    colFooter = 1;
                }

            }


            HttpResponse Response = System.Web.HttpContext.Current.Response;
            Response.Clear();
            xlPackage.SaveAs(Response.OutputStream);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename + ".xlsx");
            Response.End();
        }

    }

    private static string getWorkbookTemplate()
    {
        var sb = new StringBuilder();
        sb.Append("<xml version>\r\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
        sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n");
        sb.Append(" <Styles>\r\n");
        sb.Append("<Style ss:ID=\"myHeader\">");
        sb.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
        sb.Append("<Borders>");
        sb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\" />");
        sb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("</Borders>");
        sb.Append("<Font ss:FontName=\"Arial\" x:Family=\"Swiss\" ss:Bold=\"1\"/>");
        sb.Append("<Interior ss:Color=\"#8DB4E3\" ss:Pattern=\"Solid\"/>");
        sb.Append("<NumberFormat ss:Format=\"Standard\"/>");
        sb.Append("<Protection/>");
        sb.Append("</Style>");

        sb.Append("<Style ss:ID=\"myHeaderNumber\">");
        sb.Append("<Alignment ss:Horizontal=\"Right\" ss:Vertical=\"Center\"/>");
        sb.Append("<Borders>");
        sb.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\" />");
        sb.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\" ss:Color=\"#4F81BD\"/>");
        sb.Append("</Borders>");
        sb.Append("<Font ss:FontName=\"Arial\" x:Family=\"Swiss\" ss:Bold=\"1\"/>");
        sb.Append("<Interior ss:Color=\"#8DB4E3\" ss:Pattern=\"Solid\"/>");
        sb.Append("<NumberFormat ss:Format=\"Standard\"/>");
        sb.Append("<Protection/>");
        sb.Append("</Style>");

        sb.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n <Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
        sb.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat ss:Format=\"Standard\"/>\r\n <Protection/>\r\n </Style>\r\n <Style ss:ID=\"BoldColumn\">\r\n <Font ");
        sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n <Style ss:ID=\"s62\">\r\n <NumberFormat");
        sb.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n <Style ss:ID=\"Decimal\">\r\n <NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
        sb.Append("<Style ss:ID=\"Integer\">\r\n <NumberFormat ss:Format=\"#,##0\"/>\r\n </Style>\r\n");
        sb.Append("<Style ss:ID=\"Float\">\r\n <NumberFormat ss:Format=\"Standard\"/>\r\n </Style>\r\n <Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
        sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n <Style ss:ID=\"s28\">\r\n");
        sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\" ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");
        sb.Append("<Font x:CharSet=\"1\"  ss:Size=\"9\" ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
        sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/> </Style>\r\n</Styles>\r\n {0}</Workbook>");
        return sb.ToString();
    }
    private static string getWorksheets(DataSet source)
    {
        var sw = new StringWriter();
        if (source == null || source.Tables.Count == 0)
        {
            sw.Write("<Worksheet ss:Name=\"Sheet1\"><Table><Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row></Table></Worksheet>");
            return sw.ToString();
        }
        foreach (DataTable dt in source.Tables)
        {
            if (dt.Rows.Count == 0)
                sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
                "\"><Table><Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row></Table></Worksheet>");
            else
            {
                //write each row data
                var sheetCount = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if ((i % rowLimit) == 0)
                    {
                        //add close tags for previous sheet of the same data table
                        if ((i / rowLimit) > sheetCount)
                        {
                            sw.Write("</Table></Worksheet>");
                            sheetCount = (i / rowLimit);
                        }
                        sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName)
                            + (((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\"><Table>");
                        //write column name row
                        if (CustomHeaders != null)
                        {
                            sw.Write(getCustomHeader());
                        }
                        else
                        {
                            sw.Write("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\"> ");
                            foreach (DataColumn dc in dt.Columns)
                                sw.Write(
                                    string.Format(
                                        "<Cell ss:StyleID=\"myHeader\"><Data ss:Type=\"String\">{0}</Data></Cell>",
                                        replaceXmlChar(dc.ColumnName)));
                            sw.Write("</Row>\r\n");
                        }

                    }
                    sw.Write("<Row>\r\n");
                    List<string> headerString = new List<string>();     //Tên những cột kiểu string
                    //headerString.Add("code");
                    //ký hiệu hóa đơn, số đth, 
                    List<string> headerInteger = new List<string>();//Tên những cột kiểu int
                    headerInteger.Add("số lượng");

                    foreach (DataColumn dc in dt.Columns)
                    {
                        string value = dt.Rows[i][dc.ColumnName].ToString();
                        bool kt = false;
                        bool isInteger = false;
                        foreach (var item in headerString)
                        {
                            if (dc.ColumnName.ToLower().Contains(item))
                            {
                                kt = true;  // Columns is string
                                break;
                            }
                        }
                        foreach (var item in headerInteger)
                        {
                            if (dc.ColumnName.ToLower().Contains(item))
                            {
                                isInteger = true;  // Columns is string
                                break;
                            }
                        }
                        if (isInteger && Utils.IsNumeric(value))
                        {
                            sw.Write(string.Format("<Cell ss:StyleID=\"Integer\"><Data ss:Type=\"Number\">{0}</Data></Cell>", replaceXmlChar(value)));
                        }
                        else if (Utils.IsNumeric(value) && kt == false)
                        {
                            sw.Write(string.Format("<Cell ss:StyleID=\"Float\"><Data ss:Type=\"Number\">{0}</Data></Cell>", replaceXmlChar(value)));
                        }
                        else
                        {
                            sw.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(value)));
                        }

                    }
                    sw.Write("</Row>\r\n");

                }
                //Footer
                string footer = getCustomFooter();
                if (footer.Length != 0)
                {
                    sw.Write(footer);
                }

                sw.Write("</Table></Worksheet>");
            }
        }

        return sw.ToString();
    }
    public static string GetExcelXml(DataSet dsInput, string filename)
    {
        var excelTemplate = getWorkbookTemplate();
        var worksheets = getWorksheets(dsInput);
        var excelXml = string.Format(excelTemplate, worksheets);
        return excelXml;
    }
    public static void ToExcel
    (DataSet dsInput, string filename, HttpResponse response, bool customHeader = false)
    {

        if (!customHeader)
        {
            CustomHeaders = null;   //Is not custom header
        }
        var excelXml = GetExcelXml(dsInput, filename);
        response.Clear();
        response.AppendHeader("Content-Type", "application/vnd.ms-excel");
        response.AppendHeader("Content-disposition", "attachment; filename=" + filename);
        response.Write(excelXml);
        response.Flush();
        response.End();
    }
    public static void ToExcel
   (DataTable dtInput, string filename, HttpResponse response, bool customHeader = false)
    {
        if (!customHeader)
        {
            CustomHeaders = null;   //Is not custom header
        }
        var ds = new DataSet();
        ds.Tables.Add(dtInput.Copy());
        ToExcel(ds, filename, response, customHeader);
    }
    public static void ExportDataTableToExcelWithRadGrid(DataTable dt, RadGrid tempGrid,
        HttpResponse response, string filename = "result", List<ExcelHeader> myHeaders = null,
        List<string> myFooters = null)
    {
        CustomHeaders = myHeaders;
        CustomFooters = myFooters;
        int rgColumCount = tempGrid.Columns.Count;
        int dtColumnCount = dt.Columns.Count;

        if (myHeaders == null)
        {

            for (int i = 0; i < rgColumCount; i++)
            {
                string datafield = string.Empty;
                string x = tempGrid.Columns[i].ColumnType;
                if (tempGrid.Columns[i].ColumnType == "GridBoundColumn")
                {
                    GridBoundColumn myColumn = (GridBoundColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                else
                {
                    GridTemplateColumn myColumn = (GridTemplateColumn)tempGrid.Columns[i];
                    datafield = myColumn.DataField;
                }
                if (dt.Columns[datafield] != null && tempGrid.Columns[i].HeaderText.Length != 0 && tempGrid.Columns[i].Visible)
                {
                    dt.Columns[datafield].SetOrdinal(i);
                    dt.Columns[datafield].ColumnName = tempGrid.Columns[i].HeaderText;
                    AllowDataField.Add(tempGrid.Columns[i].HeaderText);
                }
            }
        }
        else
        {
            AllowDataField.Clear();
            foreach (var item in CustomHeaders)
            {
                AllowDataField.Add(item.DataField);
            }
            //Set Ordinal of Table
            int stt = 0;

            for (int i = 0; i < AllowDataField.Count; i++)
            {
                string datafield = myHeaders[i].DataField;
                if (datafield.Length != 0)
                {
                    dt.Columns[datafield].SetOrdinal(stt);
                    stt++;
                }
            }
        }

        //Remove columns diasable
        int pos = 0;
        while (pos < dtColumnCount)
        {
            if (!AllowDataField.Contains(dt.Columns[pos].ColumnName))
            {
                DataColumn dc = dt.Columns[pos];
                dt.Columns.Remove(dc);
                dtColumnCount--;
            }
            else
            {
                pos++;
            }
        }



        if (myHeaders == null)
        {
            ToExcel(dt, filename, response, false);
        }
        else
        {
            ToExcel(dt, filename, response, true);
        }
    }
}
public class ExcelCell
{
    public string DataField { get; set; }
    public string Value { get; set; }
    public int MergerY { get; set; }
    public int MergerX { get; set; }

    public ExcelCell() { }
    public ExcelCell(string datafield, string value)
    {
        DataField = datafield;
        Value = value;
    }
}